﻿using Dapper;
using Dapper.Contrib.Extensions;
using Microsoft.Extensions.DependencyInjection;
using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Linq.Expressions;
using System.Text;
using System.Threading.Tasks;

namespace Lynn.Infastructure.Repository.Dapper
{
    [AppIgnore]
    public class DapperBaseRepository : IDapperBaseRepository
    {
        DapperBaseContext _context;
        public DapperBaseRepository(DapperBaseContext context)
        {
            _context = context;
        }

        public async Task<bool> Delete<T>(T entity) where T : class
        {
            var sql = DeleteSql<T>();
            return await ExecuteAsync(sql, entity) > 0;
        }

        public async Task<bool> Delete<T>(IEnumerable<T> entities) where T : class
        {
            var transaction = BeginTransaction(IsolationLevel.RepeatableRead);
            var flag = false;
            try
            {
                foreach (var entity in entities)
                {
                    var sql = DeleteSql<T>();
                    flag = await ExecuteAsync(sql, entity, transaction) > 0;
                    if (flag == false)
                    {
                        transaction.Rollback();
                    }
                }
                transaction.Commit();
            }
            catch (Exception)
            {
                transaction.Rollback();
                throw;
            }
            finally
            {
                transaction.Dispose();
            }
            return flag;
        }



        public async Task<IList<T>> GetList<T>(Expression<Func<T, bool>> where) where T:class
        {
            var sql = $"SELECT * FROM {_context.TableName<T>()} WHERE {_context.Where(where)}";
            sql = _context.ReplaceSQL<T>(sql);
            var res = await QueryAsync<T>(sql);
            return res.ToList();
        }

        public IList<T> GetListPage<T>(int page, int pageSize, out int totalPage, out int totalCount, Expression<Func<T, bool>> where, Expression<Func<T, object>> order, bool orderByasc = true) where T : class
        {
            if (page < 1)
            {
                throw new Exception("页码从1开始计算");
            }
            var transaction = BeginTransaction(IsolationLevel.RepeatableRead);
            IList<T> list = null;
            try
            {
                totalCount = ExecuteScalarAsync<int>($"SELECT COUNT(1) FROM {_context.TableName<T>()} WHERE {_context.Where(where)}", null, transaction).Result;
                totalPage = (int)Math.Floor(totalCount / pageSize * 1.0m);
                string orderParam = _context.Order(order);
                string orderSql = orderByasc ? "ASC" : "DESC";
                switch (_context.baseType)
                {
                    default: break;
                    case DataBaseType.MySql:
                        {
                            //MySql
                            //重新计算偏移
                            var offset = pageSize * (page - 1);
                            var offsetSql = $"SELECT {orderParam} FROM {_context.TableName<T>()} WHERE {_context.Where(where)} ORDER BY {orderParam} {orderSql} LIMIT {offset},1";
                            var offsetV = orderByasc ? ">" : "<";
                            var sql = $"SELECT * FROM {_context.TableName<T>()} WHERE {orderParam} {offsetV} ({offsetSql}) {_context.Where(where)} ORDER BY {orderParam} {orderSql} LIMIT {pageSize}";
                            list = QueryAsync<T>(sql, null, transaction).Result;
                        }
                        break;
                }
                transaction.Commit();
            }
            catch
            {
                transaction.Rollback();
                throw;
            }
            finally
            {
                transaction.Dispose();
            }
            return list;
        }

        public async Task<bool> Instert<T>(T entity) where T:class
        {
            var id = await _context.Connection.InsertAsync(entity);
            return id > 0;
        }

        public async Task<bool> Instert<T>(IEnumerable<T> entities) where T : class
        {
            {
                var transaction = BeginTransaction(IsolationLevel.RepeatableRead);
                var flag = false;
                try
                {
                    foreach (var entity in entities)
                    {
                        var id = await _context.Connection.InsertAsync(entity, transaction);
                        flag = id > 0;
                        if (flag == false)
                        {
                            transaction.Rollback();
                        }
                    }
                    transaction.Commit();
                }
                catch (Exception)
                {
                    transaction.Rollback();
                    throw;
                }
                finally
                {
                    transaction.Dispose();
                }
                return flag;
            }
        }

        public async Task<bool> Update<T>(T entity) where T : class
        {
            var sql = UpdateSql<T>(entity);
            return await ExecuteAsync(sql, entity) > 0;
        }

        public async Task<bool> Update<T>(IEnumerable<T> entities) where T : class
        {
            var transaction = BeginTransaction(IsolationLevel.RepeatableRead);
            var flag = false;
            try
            {
                foreach (var entity in entities)
                {
                    var sql = UpdateSql<T>(entity);
                    flag = await ExecuteAsync(sql, entity, transaction) > 0;
                    if (flag == false)
                    {
                        transaction.Rollback();
                    }
                }
                transaction.Commit();
            }
            catch (Exception)
            {
                transaction.Rollback();
                throw;
            }
            finally
            {
                transaction.Dispose();
            }
            return flag;
        }

        public async Task<bool> Update<T>(Expression<Func<T, bool>> where, object param) where T : class
        {
            var whereSql = _context.Where(where);
            var build = new StringBuilder();
            build.Append("UPDATE ");
            build.Append(_context.TableName<T>());
            build.Append(" SET ");
            build.Append(_context.Set(param));
            build.Append($" WHERE {whereSql}");
            return await ExecuteAsync(build.ToString(), param) > 0;
        }
        private string UpdateSql<T>(T entity) where T : class
        {
            var build = new StringBuilder();
            build.Append("UPDATE ");
            build.Append(_context.TableName<T>());
            build.Append(" SET ");
            build.Append(_context.Set(entity));
            build.Append(" WHERE Id = @Id");
            return build.ToString();
        }
        private string DeleteSql<T>() where T : class
        {
            var build = new StringBuilder();
            build.Append("DELETE FROM ");
            build.Append(_context.TableName<T>());
            build.Append(" WHERE Id = @Id");
            return build.ToString();
        }
        #region 公共
        public async Task<IList<TResult>> QueryAsync<TResult>(string sql, object param = null, IDbTransaction transaction = null)
        {
            var str = _context.ReplaceSQL<TResult>(sql);
            var list = await _context.Connection.QueryAsync<TResult>(str, param, transaction);
            return list.ToList();
        }
        public async Task<int> ExecuteAsync(string sql, object param = null, IDbTransaction transaction = null)
        {
            var str = _context.ReplaceSQL<int>(sql);
            return await _context.Connection.ExecuteAsync(str, param, transaction);
        }

        public async Task<TResult> ExecuteScalarAsync<TResult>(string sql, object param = null, IDbTransaction transaction = null)
        {
            var str = _context.ReplaceSQL<TResult>(sql);
            return await _context.Connection.ExecuteScalarAsync<TResult>(str, param, transaction);
        }

        public IDbTransaction BeginTransaction(IsolationLevel il = IsolationLevel.RepeatableRead)
        {
            _context.Connection.Open();
            return _context.Connection.BeginTransaction(il);
        }

        #endregion
    }
}
